package com.huaxia.dao.attend;

import com.huaxia.pojo.attend.SaleDeptAttend;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/03 14:26
 */
public interface SaleDeptAttendMapper {

    @Select("select a.saledeptsname,a.saledeptDaySignNum,b.saledeptYesterdaySignNum,c.saledeptMonAvg,round(decode(b.saledeptYesterdaySignNum,0,-9999,(a.saledeptDaySignNum-b.saledeptYesterdaySignNum)*100/b.saledeptYesterdaySignNum))attendGrowth,d.saledeptNewMonSign  from\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptDaySignNum from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num from\n" +
            "(select t.managecom saledeptcode,t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd')\n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)a group by a.saledeptcode)a right join SD_SALEDEPT sb on a.saledeptcode=sb.saledeptcode\n" +
            ")a,\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptYesterdaySignNum from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num from\n" +
            "(select t.managecom saledeptcode,t.staffcode from LTATTENDANCE t where\n" +
            "(case when to_char(sysdate,'d')=2 then to_char(sysdate-2,'yyyy/mm/dd') else to_char(sysdate-1,'yyyy/mm/dd') end)=to_char(t.attendate,'yyyy/mm/dd')\n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)a group by a.saledeptcode)a right join SD_SALEDEPT sb on a.saledeptcode=sb.saledeptcode\n" +
            ")b,\n" +
            "(select a.saledeptsname,round(decode(b.num,0,0,a.saledeptMonSignNum/b.num)) saledeptMonAvg from\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptMonSignNum from\n" +
            "(select t.managecom saledeptcode ,count(t.managecom)num from  LTATTENDANCE t where  t.staffcode is not null and t.attendancetype='morning'\n" +
            "and to_char(sysdate,'yyyy/mm')=to_char(t.attendate,'yyyy/mm') and to_char(t.attendate,'d')!=1 group by  t.managecom)a right join SD_SALEDEPT sb on a.saledeptcode=sb.saledeptcode)a,\n" +
            "(select count(1)num from (\n" +
            "select to_char(TO_DATE(TO_CHAR(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') - level, 'd') DOW from dual\n" +
            "connect by level <= trunc(to_date(TO_CHAR(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE,'MM'))+1\n" +
            ") where DOW!=7)b\n" +
            ")c,\n" +
            "(select sb.saledeptsname,NVL(a.num,0)saledeptNewMonSign from\n" +
            "(select a.saledeptcode,count(a.saledeptcode)num  from\n" +
            "(select  b.saledeptcode,b.agentcode from\n" +
            "(select t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd')\n" +
            "and t.staffcode is not null group by t.staffcode)a,\n" +
            "(select  t.saledeptcode,t.agentcode from D_AGENT_PROVINCE_YX2 t where\n" +
            "to_char(t.employdate,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职' group by t.agentcode, t.saledeptcode)b\n" +
            "where a.staffcode=b.agentcode)a group by a.saledeptcode)a right join SD_SALEDEPT sb on a.saledeptcode=sb.saledeptcode)d\n" +
            "where a.saledeptsname=b.saledeptsname and b.saledeptsname=c.saledeptsname and c.saledeptsname=d.saledeptsname order by a.saledeptDaySignNum Desc\n")
    List<SaleDeptAttend> getSaleDeptAttend();
}
